#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Mnjt表Repository - 处理交易记录数据
负责mnjt表的所有数据访问操作
"""

from typing import List, Dict, Any, Optional
from datetime import date, datetime
from .base_repository import BaseRepository


class MnjtRepository(BaseRepository):
    """Mnjt表数据访问类 - 处理交易记录数据"""
    
    def __init__(self, connection):
        super().__init__(connection)
        self.table_name = "mnjt"
    
    def insert_stock_record(self, stock_data: Dict[str, Any]) -> int:
        """
        插入股票交易记录
        
        Args:
            stock_data: 股票数据字典
            
        Returns:
            int: 影响的行数
        """
        query = """
        INSERT INTO [GP].[dbo].[mnjt] 
        (code, Reason, Score, buy_jiage, buy_dtm, jiage, zhangfu, days, isend)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
        """
        
        # 处理买入日期
        buy_date = stock_data['buy_date']
        if hasattr(buy_date, 'strftime'):
            buy_date_str = buy_date.strftime('%Y-%m-%d')
        else:
            buy_date_str = str(buy_date)
        
        params = (
            stock_data['code'],
            stock_data['reason'],
            stock_data['score'],
            stock_data['buy_price'],
            buy_date_str,
            stock_data.get('close_price', 0),
            stock_data.get('change_percent', 0),
            stock_data['days'],
            stock_data['is_end']
        )
        
        self.logger.debug(f"插入股票记录: {stock_data['code']}, buy_date: {buy_date_str}")
        return self.execute_non_query(query, params)
    
    def update_stock_daily(self, stock_id: int, close_price: float, change_percent: float, days: int, is_end: str = 'N') -> int:
        """
        更新股票的每日数据
        
        Args:
            stock_id: 股票记录ID
            close_price: 收盘价
            change_percent: 涨跌幅
            days: 持仓天数
            is_end: 是否结束
            
        Returns:
            int: 影响的行数
        """
        query = """
        UPDATE [GP].[dbo].[mnjt] 
        SET jiage = ?, zhangfu = ?, days = ?, isend = ?
        WHERE id = ?
        """
        
        params = (close_price, change_percent, days, is_end, stock_id)
        return self.execute_non_query(query, params)
    
    def update_stock_daily_with_volume(self, stock_id: int, close_price: float, change_percent: float, days: int, volume: int, is_end: str = 'N') -> int:
        """
        更新股票的每日数据（包含成交量）
        
        Args:
            stock_id: 股票记录ID
            close_price: 收盘价
            change_percent: 涨跌幅
            days: 持仓天数
            volume: 成交量
            is_end: 是否结束
            
        Returns:
            int: 影响的行数
        """
        query = """
        UPDATE [GP].[dbo].[mnjt] 
        SET jiage = ?, zhangfu = ?, days = ?, chengjiaoliang = ?, isend = ?
        WHERE id = ?
        """
        
        params = (close_price, change_percent, days, volume, is_end, stock_id)
        return self.execute_non_query(query, params)
    
    def get_previous_volumes_from_chengjiaoliang(self, stock_codes: List[str], target_date: date) -> Dict[str, int]:
        """
        从chengjiaoliang字段获取指定日期的成交量数据
        
        Args:
            stock_codes: 股票代码列表
            target_date: 目标日期
            
        Returns:
            Dict[str, int]: 股票代码到成交量的映射
        """
        if not stock_codes:
            return {}
        
        # 构造IN子句的占位符
        placeholders = ','.join(['?' for _ in stock_codes])
        
        query = f"""
        SELECT code, chengjiaoliang
        FROM [GP].[dbo].[mnjt] 
        WHERE code IN ({placeholders}) 
        AND CAST(buy_dtm AS DATE) = ?
        AND chengjiaoliang IS NOT NULL
        AND chengjiaoliang > 0
        """
        
        params = stock_codes + [target_date]
        
        try:
            results = self.execute_query(query, params)
            volume_map = {}
            for row in results:
                code = row.get('code')
                volume = row.get('chengjiaoliang')
                if code and volume:
                    volume_map[code] = int(volume)
            
            self.logger.debug(f"从chengjiaoliang字段获取到 {len(volume_map)} 只股票的成交量数据")
            return volume_map
            
        except Exception as e:
            self.logger.error(f"从chengjiaoliang字段获取成交量失败: {e}")
            return {}
    
    def get_existing_codes_by_date(self, stock_codes: List[str], target_date: date) -> set:
        """
        查询指定日期在mnjt表中存在的股票代码
        
        Args:
            stock_codes: 要查询的股票代码列表
            target_date: 目标日期
            
        Returns:
            set: 在该日期存在的股票代码集合
        """
        if not stock_codes:
            return set()
        
        # 构造IN子句的占位符
        placeholders = ','.join(['?' for _ in stock_codes])
        
        query = f"""
        SELECT DISTINCT code
        FROM [GP].[dbo].[mnjt] 
        WHERE code IN ({placeholders}) 
        AND CAST(buy_dtm AS DATE) = ?
        """
        
        params = stock_codes + [target_date]
        
        try:
            results = self.execute_query(query, params)
            existing_codes = {row['code'] for row in results if row.get('code')}
            self.logger.debug(f"查询到 {len(existing_codes)} 只股票在 {target_date} 存在于mnjt表")
            return existing_codes
            
        except Exception as e:
            self.logger.error(f"查询股票存在性失败: {e}")
            return set()
    
    def get_active_stocks(self) -> List[Dict[str, Any]]:
        """
        获取所有正在监听的股票（isend != 'Y'）
        
        Returns:
            List[Dict]: 活跃股票记录列表
        """
        query = """
        SELECT id, code, Reason, Score, buy_jiage, buy_dtm, jiage, zhangfu, days, isend, chengjiaoliang
        FROM [GP].[dbo].[mnjt] 
        WHERE isend != 'Y' OR isend IS NULL
        ORDER BY buy_dtm DESC, code
        """
        
        return self.execute_query(query)
    
    def get_stocks_bought_on_date(self, target_date: date) -> List[Dict[str, Any]]:
        """
        获取在指定日期买入的股票记录
        
        Args:
            target_date: 目标买入日期
            
        Returns:
            List[Dict]: 指定日期买入的股票记录列表
        """
        query = """
        SELECT id, code, Reason, Score, buy_jiage, buy_dtm, jiage, zhangfu, days, isend, chengjiaoliang
        FROM [GP].[dbo].[mnjt] 
        WHERE CAST(buy_dtm AS DATE) = ? AND (isend != 'Y' OR isend IS NULL)
        ORDER BY code
        """
        
        return self.execute_query(query, (target_date,))
    
    def get_first_buy_date_for_stock(self, stock_code: str) -> Optional[date]:
        """
        获取指定股票的第一次买入日期（仅限未结束的记录）
        
        Args:
            stock_code: 股票代码
            
        Returns:
            date: 该股票当前监听周期的第一次买入日期，如果没有未结束的记录返回None
        """
        query = """
        SELECT MIN(CAST(buy_dtm AS DATE)) as first_buy_date
        FROM [GP].[dbo].[mnjt] 
        WHERE code = ? AND (isend != 'Y' OR isend IS NULL)
        """
        
        result = self.execute_query(query, (stock_code,))
        if result and result[0]['first_buy_date']:
            first_date = result[0]['first_buy_date']
            # 确保返回的是 date 类型
            if isinstance(first_date, str):
                return datetime.strptime(first_date, '%Y-%m-%d').date()
            elif hasattr(first_date, 'date'):
                return first_date.date()
            else:
                return first_date
        return None
    
    def get_active_monitoring_stocks(self) -> List[Dict[str, Any]]:
        """
        获取所有正在监听中的股票（用于买入任务的持续监听）
        
        Returns:
            List[Dict]: 需要持续监听的股票列表，包含code和reason字段
        """
        query = """
        SELECT code, Reason, buy_jiage, days
        FROM (
            SELECT code, Reason, buy_jiage, days,
                   ROW_NUMBER() OVER (PARTITION BY code ORDER BY id DESC) as rn
            FROM [GP].[dbo].[mnjt] 
            WHERE isend != 'Y' OR isend IS NULL
        ) ranked
        WHERE rn = 1
        """
        
        return self.execute_query(query)
    
    def get_stocks_by_buy_date_sorted_by_score(self, target_date: date) -> List[Dict[str, Any]]:
        """
        获取指定买入日期的所有股票记录，按Score降序排列
        
        Args:
            target_date: 买入日期
            
        Returns:
            List[Dict]: 按Score降序排列的股票记录列表
        """
        query = """
        SELECT id, code, Reason, Score, buy_jiage, buy_dtm, jiage, zhangfu, days, isend, chengjiaoliang
        FROM [GP].[dbo].[mnjt] 
        WHERE CAST(buy_dtm AS DATE) = ?
        ORDER BY Score DESC
        """
        
        return self.execute_query(query, (target_date,))
    
    def check_stock_exists_on_date(self, stock_code: str, buy_date: date) -> bool:
        """
        检查股票在指定日期是否已经买入过
        
        Args:
            stock_code: 股票代码
            buy_date: 买入日期
            
        Returns:
            bool: True表示已存在，False表示不存在
        """
        query = """
        SELECT COUNT(1) as count 
        FROM [GP].[dbo].[mnjt] 
        WHERE code = ? AND CAST(buy_dtm AS DATE) = ?
        """
        
        result = self.execute_query(query, (stock_code, buy_date))
        return result[0]['count'] > 0 if result else False
    
    def has_buy_records_for_date(self, target_date: date) -> bool:
        """
        检查指定日期是否有买入记录（用于补触发判定）
        
        Args:
            target_date: 目标日期
            
        Returns:
            bool: 是否有买入记录
        """
        query = """
        SELECT COUNT(1) as cnt
        FROM [GP].[dbo].[mnjt]
        WHERE CAST(buy_dtm AS DATE) = ?
        """
        
        result = self.execute_query(query, (target_date,))
        return (result and result[0].get('cnt', 0) > 0)
    
    def get_stock_monitoring_history(self, stock_code: str, days: int = 10) -> List[Dict[str, Any]]:
        """
        获取股票的连续监听历史记录
        
        Args:
            stock_code: 股票代码
            days: 查询天数
            
        Returns:
            List[Dict]: 监听历史记录
        """
        query = """
        SELECT TOP (?) 
            id, code, Reason, Score, buy_jiage, buy_dtm, 
            jiage, zhangfu, days, isend, chengjiaoliang
        FROM [GP].[dbo].[mnjt] 
        WHERE code = ?
        ORDER BY buy_dtm DESC
        """
        
        return self.execute_query(query, (days, stock_code))
    
    def get_performance_summary(self, start_date: Optional[date] = None, end_date: Optional[date] = None) -> Dict[str, Any]:
        """
        获取交易性能汇总统计
        
        Args:
            start_date: 开始日期
            end_date: 结束日期
            
        Returns:
            Dict: 性能统计数据
        """
        where_clause = ""
        params = []
        
        if start_date and end_date:
            where_clause = "WHERE buy_dtm >= ? AND buy_dtm <= ?"
            params = [start_date, end_date]
        elif start_date:
            where_clause = "WHERE buy_dtm >= ?"
            params = [start_date]
        elif end_date:
            where_clause = "WHERE buy_dtm <= ?"
            params = [end_date]
        
        query = f"""
        SELECT 
            COUNT(*) as total_trades,
            COUNT(CASE WHEN zhangfu > 0 THEN 1 END) as winning_trades,
            AVG(zhangfu) as avg_return,
            MAX(zhangfu) as max_return,
            MIN(zhangfu) as min_return,
            SUM(CASE WHEN zhangfu > 0 THEN zhangfu ELSE 0 END) as total_profit,
            SUM(CASE WHEN zhangfu < 0 THEN zhangfu ELSE 0 END) as total_loss,
            AVG(days) as avg_holding_days
        FROM [GP].[dbo].[mnjt]
        {where_clause}
        """
        
        result = self.execute_query(query, tuple(params))
        return result[0] if result else {}
    
    def get_recent_trades(self, limit: int = 20) -> List[Dict[str, Any]]:
        """
        获取最近的交易记录
        
        Args:
            limit: 返回记录数量
            
        Returns:
            List[Dict]: 最近交易记录
        """
        query = f"""
        SELECT TOP ({limit})
            id, code, Reason, Score, buy_jiage, buy_dtm,
            jiage, zhangfu, days, isend, chengjiaoliang
        FROM [GP].[dbo].[mnjt]
        ORDER BY id DESC
        """
        
        return self.execute_query(query)
    
    def get_current_holdings(self) -> List[Dict[str, Any]]:
        """
        获取当前持仓股票
        
        Returns:
            List[Dict]: 当前持仓记录
        """
        query = """
        SELECT 
            id, code, Reason, Score, buy_jiage, buy_dtm,
            jiage, zhangfu, days, isend, chengjiaoliang
        FROM [GP].[dbo].[mnjt]
        WHERE isend != 'Y' OR isend IS NULL
        ORDER BY buy_dtm DESC
        """
        
        return self.execute_query(query)
    
    def get_daily_performance(self, days: int = 30) -> List[Dict[str, Any]]:
        """
        获取每日交易性能数据
        
        Args:
            days: 查询天数
            
        Returns:
            List[Dict]: 每日性能数据
        """
        query = f"""
        SELECT 
            CAST(buy_dtm AS DATE) as trade_date,
            COUNT(*) as trade_count,
            AVG(zhangfu) as avg_return,
            SUM(zhangfu) as total_return,
            COUNT(CASE WHEN zhangfu > 0 THEN 1 END) as winning_count
        FROM [GP].[dbo].[mnjt]
        WHERE buy_dtm >= DATEADD(day, -{days}, GETDATE())
        GROUP BY CAST(buy_dtm AS DATE)
        ORDER BY trade_date
        """
        
        return self.execute_query(query)
    
    def get_stock_performance_ranking(self, limit: int = 50) -> List[Dict[str, Any]]:
        """
        获取股票收益排行榜
        
        Args:
            limit: 返回数量
            
        Returns:
            List[Dict]: 股票收益排行
        """
        query = f"""
        SELECT TOP ({limit})
            code,
            COUNT(*) as trade_count,
            AVG(zhangfu) as avg_return,
            MAX(zhangfu) as best_return,
            MIN(zhangfu) as worst_return,
            COUNT(CASE WHEN zhangfu > 0 THEN 1 END) as win_count
        FROM [GP].[dbo].[mnjt]
        GROUP BY code
        HAVING COUNT(*) >= 3  -- 至少交易3次
        ORDER BY avg_return DESC
        """
        
        return self.execute_query(query)
    
    def delete_stock_record(self, record_id: int) -> int:
        """
        删除股票记录
        
        Args:
            record_id: 记录ID
            
        Returns:
            int: 影响的行数
        """
        query = "DELETE FROM [GP].[dbo].[mnjt] WHERE id = ?"
        return self.execute_non_query(query, (record_id,))
    
    def cleanup_old_records(self, days_to_keep: int = 365) -> int:
        """
        清理旧记录
        
        Args:
            days_to_keep: 保留天数
            
        Returns:
            int: 删除的记录数
        """
        query = f"""
        DELETE FROM [GP].[dbo].[mnjt]
        WHERE buy_dtm < DATEADD(day, -{days_to_keep}, GETDATE())
        AND isend = 'Y'
        """
        
        return self.execute_non_query(query)
    
    def batch_update_chengjiaoliang(self, volume_data: Dict[str, int], target_date: date) -> int:
        """
        批量更新chengjiaoliang字段
        
        Args:
            volume_data: 股票代码到成交量的映射字典
            target_date: 目标日期
            
        Returns:
            int: 影响的行数
        """
        if not volume_data:
            return 0
        
        # 过滤有效数据
        valid_data = {code: volume for code, volume in volume_data.items() 
                     if volume is not None and volume > 0}
        
        if not valid_data:
            return 0
        
        # 构造CASE WHEN语句
        when_clauses = []
        all_params = []
        
        for code, volume in valid_data.items():
            when_clauses.append("WHEN code = ? THEN ?")
            all_params.extend([code, volume])
        
        # 构造完整SQL（不需要IN条件，CASE已经包含了所有需要更新的股票）
        query = f"""
        UPDATE [GP].[dbo].[mnjt] 
        SET chengjiaoliang = CASE 
            {' '.join(when_clauses)}
            ELSE chengjiaoliang 
        END
        WHERE CAST(buy_dtm AS DATE) = ?
        """
        
        # 构造参数：只需要CASE参数 + 日期参数
        params = all_params + [target_date]
        
        try:
            # 调试：输出SQL和参数信息
            self.logger.debug(f"执行SQL: {query}")
            self.logger.debug(f"参数数量: {len(params)}, CASE参数: {len(all_params)}, 日期参数: 1")
            
            updated_count = self.execute_non_query(query, params)
            self.logger.info(f"批量更新完成，总共更新了 {updated_count} 条记录")
            return updated_count
        except Exception as e:
            self.logger.error(f"批量更新chengjiaoliang字段失败: {e}")
            self.logger.error(f"SQL: {query}")
            self.logger.error(f"参数数量: {len(params)}")
            return 0